package nc.sql.utli;

import java.util.List;
import java.util.Map;

import nc.bs.dao.BaseDAO;
import nc.bs.dao.DAOException;
import nc.bs.pub.util.BaseDaoUtil;
import nc.jdbc.framework.processor.BeanListProcessor;
import nc.jdbc.framework.processor.ColumnListProcessor;
import nc.jdbc.framework.processor.ColumnProcessor;
import nc.jdbc.framework.processor.MapListProcessor;
import nc.vo.pu.balancer.BalancerBVO;


public class SqlUtli {

	
	public static BaseDAO getBasedao() {

		return BaseDaoUtil.getInstance();
	}
	
	
	  public static List<BalancerBVO> getBvos(String sql) throws DAOException{
		  List<BalancerBVO> balancerBVO = (List<BalancerBVO>) getBasedao().executeQuery(sql, new BeanListProcessor(BalancerBVO.class));
		  
		  return balancerBVO;
	  }
	  public static List<Map<String, Object>> getfreemapMap(String sql) throws DAOException{
		  List<Map<String, Object>> balancerBVO = (List<Map<String, Object>>) getBasedao().executeQuery(sql, new MapListProcessor());
		  
		  return balancerBVO;
	  }
	  public static List<String> getfreemapList(String sql) throws DAOException{
		  List<String> balancerBVO = (List<String>) getBasedao().executeQuery(sql, new ColumnListProcessor());
		  
		  return balancerBVO;
	  }
	  public static String getSqlString(String sql) throws DAOException{
		  String balancerBVO = (String) getBasedao().executeQuery(sql, new ColumnProcessor());
		  if (balancerBVO==null) {
			  return "";
		} 
		  return balancerBVO;
	  }
	  public static String getSql(String org,String book,String yera,String mth,String gl_docfree1) {

			return "SELECT\r\n" + 
					"	kmye.pk_accountingbook,\r\n" + 
					"	kmye.pk_accasoa,\r\n" + 
					"	kmye.year,\r\n" + 
					"	kmye.assid bdef1,\r\n" + 
					"	kmye.ncsubject,\r\n" + 
					"	kmye.subject,\r\n" + 
					"	kmye.org,\r\n" + 
					"	kmye.def1 ledgename,\r\n" + 
					"	kmye.pk_org	,\r\n" + 
					"	kmye.openingdebit,\r\n" + 
					"	kmye.currentdebit,\r\n" + 
					"	kmye.endingdebit,\r\n" + 
					"	kmye.openingcredit,\r\n" + 
					"	kmye.currentcredit,\r\n" + 
					"	kmye.endingcredit,\r\n" + 
					"	gz.closeorgpks,\r\n" + 
					"	gz.accperiodmth,\r\n" + 
					"	gz.periodyear,\r\n" + 
					"   (CASE WHEN 0=0 THEN '"+mth+"' ELSE '0' END ) accounting,\r\n" + 
					"	(CASE WHEN gz.isclose= 'Y' OR gz.ispreclose= 'Y' THEN 'Y' ELSE 'N' END ) identification,\r\n" + 
					"	gz.isclose,\r\n" + 
					"	gz.ispreclose \r\n" + 
					"FROM\r\n" + 
					"	(\r\n" + 
					"	SELECT\r\n" + 
					"		gl_balan.pk_accountingbook ,\r\n" + 
					"		gl_balan.pk_accasoa ,\r\n" + 
					"		gl_balan.year,\r\n" + 
					"		gl_balan.assid,\r\n" + 
					"		bd_accasoa.pk_account ncsubject,\r\n" + 
					"		bd_accasoa.def1 subject,\r\n" + 
					"		org_orgs.code org,\r\n" + 
					"		org_orgs.def1,\r\n" + 
					"		gl_balan.pk_org,\r\n" + 
					"	SUM ( CASE WHEN adjustperiod >= N'00' AND adjustperiod < N'"+mth+"' THEN localdebitamount ELSE 0 END ) openingdebit,\r\n" + 
					"	SUM ( CASE WHEN adjustperiod >= N'"+mth+"' AND adjustperiod <= N'"+mth+"' THEN localdebitamount ELSE 0 END ) currentdebit,\r\n" + 
					"	SUM ( CASE WHEN adjustperiod >= N'00' AND adjustperiod <= N'"+mth+"' THEN localdebitamount ELSE 0 END ) endingdebit,\r\n" + 
					"	SUM ( CASE WHEN adjustperiod >= N'00' AND adjustperiod < N'"+mth+"' THEN localcreditamount ELSE 0 END ) openingcredit,\r\n" + 
					"	SUM ( CASE WHEN adjustperiod >= N'"+mth+"' AND adjustperiod <= N'"+mth+"' THEN localcreditamount ELSE 0 END ) currentcredit,\r\n" + 
					"	SUM ( CASE WHEN adjustperiod >= N'00' AND adjustperiod <= N'"+mth+"' THEN localcreditamount ELSE 0 END ) endingcredit \r\n" + 
					"FROM\r\n" + 
					"	gl_balance gl_balan,\r\n" + 
					"	( SELECT a.pk_accasoa FROM bd_account t, bd_accasoa a WHERE t.pk_account = a.pk_account ) view_accasoa,\r\n" + 
					"	org_orgs,\r\n" + 
					"	bd_accasoa \r\n" + 
					"WHERE\r\n" + 
					"	gl_balan.pk_accasoa = view_accasoa.pk_accasoa \r\n" + 
					"	AND org_orgs.pk_org= gl_balan.pk_org \r\n" + 
					"	AND org_orgs.pk_org='"+org+"' \r\n" + 
					"	AND gl_balan.year='"+yera+"' \r\n" + 
					"	AND gl_balan.pk_accountingbook in "+book+" \r\n" + 
					"	AND gl_balan.dr='0'\r\n" + 
					"	AND bd_accasoa.pk_accasoa= gl_balan.pk_accasoa \r\n" + 
					"GROUP BY\r\n" + 
					"	gl_balan.pk_accountingbook ,\r\n" + 
					"	gl_balan.pk_accasoa,\r\n" + 
					"	gl_balan.year,\r\n" + 
					"	gl_balan.pk_org,\r\n" + 
					"	gl_balan.assid,\r\n" + 
					"	org_orgs.def1,\r\n" + 
					"	org_orgs.code,\r\n" + 
					"	bd_accasoa.pk_account, \r\n" + 
					"	bd_accasoa.def1 \r\n" + 
					"	) AS kmye\r\n" + 
					"	LEFT JOIN (\r\n" + 
					"	SELECT DISTINCT\r\n" + 
					"		o.pk_org,\r\n" + 
					"		o.closeorgpks,\r\n" + 
					"		b.accperiodmth,\r\n" + 
					"		a.periodyear,\r\n" + 
					"		o.isclose,\r\n" + 
					"		o.ispreclose \r\n" + 
					"	FROM\r\n" + 
					"		org_closeacc o,\r\n" + 
					"		bd_accperiodmonth b,\r\n" + 
					"		bd_accperiod a \r\n" + 
					"	WHERE\r\n" + 
					"		b.pk_accperiodmonth= o.pk_accperiodmonth \r\n" + 
					"		AND a.pk_accperiod= b.pk_accperiod \r\n" + 
					"	) gz ON kmye.pk_org= gz.pk_org \r\n" + 
					"	AND kmye.year = gz.periodyear \r\n" + 
					"	AND gz.accperiodmth= '"+mth+"' \r\n" + 
					"	AND kmye.pk_accountingbook= gz.closeorgpks \r\n" + 
					"	LEFT JOIN gl_docfree1 on kmye.assid =gl_docfree1.assid \r\n"+
					" 	WHERE (kmye.openingdebit>0 or kmye.currentdebit>0 or kmye.endingdebit>0 or kmye.openingcredit>0 or kmye.currentcredit>0 or kmye.endingcredit>0) \r\n"+
					"ORDER BY\r\n" + 
					"	kmye.pk_accountingbook ,\r\n" + 
					"	kmye.pk_accasoa,\r\n" + 
					"	kmye.year";
	   }
	  
	  
	  
	  public static String getSql(String code) {
		  
		  return "SELECT\r\n" + 
		  		"	( ' SELECT f' + CONVERT ( VARCHAR, a.num ) + ' FROM gl_docfree1 WHERE dr=0 ' ) AS sql  \r\n" + 
		  		"FROM\r\n" + 
		  		"	( SELECT doccode, docname, num FROM fi_freemap WHERE mod = N'00' AND doccode IN ( '"+code+"' ) ) a"; 
	  }
	  
		public static List<Map<String,String>> getdisplayName(String code,String pk) throws DAOException{
			String sql = "select \r\n" +
					"(case ttable.name when 'bd_defdoc' then ('select '' "+code+" '' as typecode, ' + tpk.name + ' as pk, ' + tcode.name + ' as code, ' + tname.name + ' as name from ' + ttable.name + ' where dr=0 and ' + tcode.name + ' ='' + value + '' and pk_defdoclist='''+ttable.classid+'''   ') else ('select '' "+code+" '' as typecode, ' + tpk.name + ' as pk, ' + tcode.name + ' as code, ' + tname.name + ' as name from ' + ttable.name + ' where dr=0 and ' + tpk.name + ' =''"+pk+"''   ') end) as sql1, ttable.display from (\r\n" +
					"     select md_property.name\r\n" +
					"      from md_property\r\n" +
					"     left join md_bizitfmap on md_bizitfmap.classattrid=md_property.id\r\n" +
					"     left join bd_accassitem on bd_accassitem.classid=md_bizitfmap.classid\r\n" +
					"     left join md_class on md_class.id=bd_accassitem.classid\r\n" +
					"     where md_bizitfmap.intattrid = '5dd3c721-22ad-42b1-9c10-4351c236bc77' and bd_accassitem.code='"+code+"'\r\n" +
					"    ) tpk,\r\n" +
					"    (\r\n" +
					"     select md_property.name\r\n" +
					"      from md_property\r\n" +
					"     left join md_bizitfmap on md_bizitfmap.classattrid=md_property.id\r\n" +
					"     left join bd_accassitem on bd_accassitem.classid=md_bizitfmap.classid\r\n" +
					"     left join md_class on md_class.id=bd_accassitem.classid\r\n" +
					"     where md_bizitfmap.intattrid = 'd32cc17b-f415-415a-923f-0764443eb102' and bd_accassitem.code='"+code+"'\r\n" +
					"    ) tcode,\r\n" +
					"    (\r\n" +
					"     select md_property.name\r\n" +
					"      from md_property\r\n" +
					"     left join md_bizitfmap on md_bizitfmap.classattrid=md_property.id\r\n" +
					"     left join bd_accassitem on bd_accassitem.classid=md_bizitfmap.classid\r\n" +
					"     left join md_class on md_class.id=bd_accassitem.classid\r\n" +
					"     where md_bizitfmap.intattrid = '89578a97-42fe-439b-827c-8eabd9e3604c' and bd_accassitem.code='"+code+"'\r\n" +
					"    ) tname,\r\n" +
					"    (\r\n" +
					"     select md_class.defaulttablename as name, bd_accassitem.name as display, bd_accassitem.classid\r\n" +
					"     from md_class\r\n" +
					"     left join bd_accassitem on md_class.id=bd_accassitem.classid\r\n" +
					"     where bd_accassitem.code='"+code+"'\r\n" +
					"    ) ttable";
			List<Map<String, String>> result = (List<Map<String, String>>) getBasedao().executeQuery(sql, new MapListProcessor());
			return result;
		}
}
